﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Navigant.Infrastructure;
using Navigant.Infrastructure.Cases;
using Navigant.Infrastructure.EntityFactoryFramework;
using Navigant.Infrastructure.RepositoryFramework;
using Navigant.Model.Custodians;
using Navigant.Model.PreservationNotices;
using System.Text;

namespace Navigant.UnitTests.MockCustodianSqlRepository
{
    public class MockSqlCustodianRepository
        : RepositoryBase<Custodian>, ICustodianRepository
    {
        #region Private Fields

        private ICase currentCase;
        private string connectionString;
        private IEntityFactory<Custodian> entityFactory;

        #endregion

        #region Public Constructors

        public MockSqlCustodianRepository(ICase currentCase)
            : this(currentCase, null)
        {
        }

        public MockSqlCustodianRepository(ICase currentCase, IUnitOfWork unitOfWork)
            : base(unitOfWork)
        {
            this.currentCase = currentCase;
            this.connectionString = ConfigurationManager.ConnectionStrings["MockNavigantPortal"].ConnectionString;
            this.entityFactory = EntityFactoryBuilder.BuildFactory<Custodian>();
        }

        #endregion

        #region Protected Methods

        protected override int PersistNewItem(Custodian item)
        {
            int custodianId = 0;
            if (item != null)
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    StringBuilder sqlBuilder = new StringBuilder();
                    //sqlBuilder.Append("DECLARE @CustodianID int ");
                    sqlBuilder.Append(string.Format("INSERT INTO Custodian (Name, Email, PreservationNoticeStatus) VALUES ('{0}', '{1}', '{2}')", 
                        item.Name.ToString(), item.Email, item.Status.ToString()));
                    sqlBuilder.Append("SET @CustodianID = SCOPE_IDENTITY()");
                    using (SqlCommand command = new SqlCommand(sqlBuilder.ToString(),
                               connection))
                    {
                        command.CommandType = CommandType.Text;
                        SqlParameter parameter = new SqlParameter("@CustodianID", SqlDbType.Int);
                        parameter.Direction = ParameterDirection.Output;
                        command.Parameters.Add(parameter);
                        connection.Open();
                        command.ExecuteNonQuery();
                        custodianId = Convert.ToInt32(command.Parameters["@CustodianID"].Value);
                    }
                }
            }
            return custodianId;
        }

        protected override void PersistUpdatedItem(Custodian item)
        {
            if (item != null)
            {
                using (SqlConnection connection = new SqlConnection(this.connectionString))
                {
                    string sql = string.Format("UPDATE Custodian SET Name = '{0}', Email = '{1}', PreservationNoticeStatus = '{2}'", 
                        item.Name.ToString(), item.Email, item.Status.ToString());
                    using (SqlCommand command = new SqlCommand(sql,
                               connection))
                    {
                        command.CommandType = CommandType.Text;
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        protected override void PersistDeletedItem(Custodian item)
        {
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                string sql = string.Format("DELETE FROM Custodian WHERE CustodianID = {0}", item.Id);
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    command.ExecuteNonQuery();
                }
            }
        }

        #endregion

        #region ICustodianRepository Members

        public IList<Custodian> FindByCriteria(PreservationNoticeCriteria criteria)
        {
            throw new NotImplementedException();
        }

        public IList<Custodian> FindByExtraCriteria(string extraCriteriaName, string extraCriteriaValue, bool contains)
        {
            throw new NotImplementedException();
        }

        public IList<string> GetExtraCriteriaPossibleValues(string extraCriteriaFieldName)
        {
            throw new NotImplementedException();
        }

        public string GetDefaultPossibleValue(string extraCriteriaFieldName)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IExtendedPropertiesRepository<Custodian> Members

        public void LoadExtendedProperties(Custodian entity)
        {
        }

        #endregion

        public override Custodian FindBy(object key)
        {
            Custodian custodian = null;
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                string sql = string.Format("SELECT * FROM Custodian WHERE CustodianID = {0}", 
                    key.ToString());
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        if (reader.Read())
                        {
                            custodian = this.entityFactory.BuildEntity(reader, this.currentCase);
                        }
                    }
                }
            }
            return custodian;
        }

        public override IList<Custodian> FindAll()
        {
            List<Custodian> custodians = new List<Custodian>();
            using (SqlConnection connection = new SqlConnection(this.connectionString))
            {
                string sql = "SELECT * FROM Custodian";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    connection.Open();
                    using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (reader.Read())
                        {
                            custodians.Add(this.entityFactory.BuildEntity(reader, this.currentCase));
                        }
                    }
                }
            }
            return custodians;
        }


        public IList<Custodian> FindAllPaged(int pageSize, int currentPage, int lastItemId, out int itemCount)
        {
            throw new NotImplementedException();
        }
    }
}